package model
{
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	
	import mx.controls.Alert;
	import mx.controls.DataGrid;
	import mx.managers.CursorManager;
	
	public class ProductSuppliers extends Inventory
	{
		[Bindable] public var dg_prodsupps:DataGrid;
		private var _product_pk:String;
		private var _pk:String;
		[Bindable] private var _dataProvider:Array;
		private var sql_statement:SQLStatement;
		private var suppliers:Suppliers;
		private static const CREATE_PRODUCT_SUPPLIER:String =
			"CREATE TABLE IF NOT EXISTS product_suppliers (pk BIGINT, product_pk BIGINT)";
			
		
		public function ProductSuppliers(s_product_pk:String = "", s_pk:String = "")
		{
			suppliers = new Suppliers();
			_product_pk = s_product_pk;
			_pk = s_pk;
			super();
			func_create_table(CREATE_PRODUCT_SUPPLIER);
			if (_pk.length <= 0) func_queryall(_product_pk);
			else func_queryall_prods(_pk);
		}
		 
		
		/**
		 * retrieve all product suppliers
		*/
		public function func_queryall(s_product_pk:String):void {
			CursorManager.setBusyCursor();
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.text = "SELECT s.id, s.pk, s.name as name, s.contact, s.contact_number FROM product_suppliers ps, suppliers s WHERE product_pk = :product_pk AND s.pk = ps.pk";
			sql_statement.parameters[":product_pk"] = s_product_pk;
			sql_statement.execute();
			func_populate_product_suppliers(sql_statement.getResult());
			CursorManager.removeBusyCursor();
		}
		/**
		 * retrieve all products that supplier supplies
		*/
		public function func_queryall_prods(s_pk:String):void {
			CursorManager.setBusyCursor();
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.text = "SELECT p.id, p.pk, p.description, p.reorder_quantity, p.available_quantity, p.unit_cost FROM product_suppliers ps, products p WHERE ps.pk = :pk AND p.pk = ps.product_pk";
			sql_statement.parameters[":pk"] = s_pk;
			sql_statement.execute();
			func_populate_product_suppliers(sql_statement.getResult());
			CursorManager.removeBusyCursor();
		}
		
		private function func_populate_product_suppliers(result:SQLResult):void {
			dataProvider = result.data;
		}
		
		public function set dataProvider(results:Array):void {
			_dataProvider = results;
		}
		
		public function get dataProvider():Array {
			return _dataProvider;
		}
		/**
		 * adds new supplier - product relationship
		*/
		public function func_add_new(str_supplier_pk:String, str_product_pk:String):void {
			sql_statement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.addEventListener(SQLErrorEvent.ERROR, hdlr_error_insertion);
			sql_statement.text = "INSERT INTO product_suppliers(pk, product_pk) VALUES(:pk, :product_pk)";
			sql_statement.parameters[":pk"] = str_supplier_pk;
			sql_statement.parameters[":product_pk"] = str_product_pk;
			
			sql_statement.execute();
		}
		private function hdlr_error_insertion(evt:SQLErrorEvent):void {
			Alert.show(evt.target.toString());
		}
		
		/**
		 * adds new supplier - product relationship
		*/
		public function func_remove_suppliers(str_product_pk:String):void {
			sql_statement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.text = "DELETE FROM product_suppliers WHERE product_pk = :product_pk";
			sql_statement.parameters[":product_pk"] = str_product_pk;
			sql_statement.execute();
		}
		
		private function func_error_queryall(evt:SQLErrorEvent):void {
			Alert.show(evt.toString());
		}
	}
}